﻿using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using OfficeOpenXml;
using System.IO;
using UnityEngine.UI;

public static class ExcelToolKit
{
    static string outPutDir = Application.streamingAssetsPath + "/MyExcel.xls";

    public static void CreateExcel()
    {
        FileInfo newFile = new FileInfo(outPutDir);
        if (newFile.Exists)
        {
            newFile.Delete();
            Debug.Log("删除表");
            newFile = new FileInfo(outPutDir);
        }
        using (ExcelPackage package = new ExcelPackage(newFile))
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Excel");
            worksheet.Cells[1, 1].Value = "序号";
            worksheet.Cells[1, 2].Value = "姓名";
            worksheet.Cells[1, 3].Value = "电话";

            package.Save();
            Debug.Log("导出Excel成功");
        }
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="excelPath"></param>
    /// <param name="jsonPath"></param>

    public static void Excel2JsonFile(string excelPath, string jsonPath)
    {
        FileToolKit.CheckAndCreateFile(jsonPath);

        StreamWriter sw = new StreamWriter(jsonPath);
        //Debug.Log(jsonPath);
        sw.WriteLine("{");
        sw.WriteLine("\t\"List\":");
        sw.WriteLine("\t[");

        var fileInfo = new FileInfo(excelPath);
        string tmpExcelFile = excelPath + ".temp";
        Debug.Log("excelPath = " + excelPath);
        Debug.Log("tmpExcelFile = " + tmpExcelFile);
        File.Copy(excelPath, tmpExcelFile, true);

        FileStream fs = new FileStream(tmpExcelFile, FileMode.Open);
        using (ExcelPackage package = new ExcelPackage(fs))
        {
            for (int i = 1; i <= package.Workbook.Worksheets.Count; ++i)
            {
                ExcelWorksheet sheet = package.Workbook.Worksheets[i];
                //Debug.Log(sheet.Dimension.End.Row);
                //Debug.Log(sheet.Dimension.End.Column);
                for (int m = sheet.Dimension.Start.Row + 2, n = sheet.Dimension.End.Row; m <= n; m++)
                {
                    sw.WriteLine("\t\t{");
                    for (int j = sheet.Dimension.Start.Column, k = sheet.Dimension.End.Column; j <= k; j++)
                    {
                        //TODO  判断String和Int
                        string str = "";
                        //Debug.Log(j);
                        if (sheet.GetValue(2, j).ToString() == "string")
                        {
                            string strasd = "";
                            if(sheet.GetValue(m, j) !=null)
                            strasd = sheet.GetValue(m, j).ToString();
                            //strasd.IndexOf(",");
                            str = "\t\t\t\"" + sheet.GetValue(1, j).ToString() + "\":" + "\"" + strasd.Replace("\r", string.Empty)
                                .Replace("\n", string.Empty).Replace("\t", string.Empty) + "\"";
                        }
                        else if(sheet.GetValue(2, j).ToString() == "int")
                        {
                            string strasd = "0";
                            if (sheet.GetValue(m, j) != null)
                                strasd = sheet.GetValue(m, j).ToString();
                            str = "\t\t\t\"" + sheet.GetValue(1, j).ToString() + "\":" + strasd;
                        }
                        else if (sheet.GetValue(2, j).ToString() == "bool")
                        {
                            string strasd = "false";
                            if (sheet.GetValue(m, j) != null)
                                strasd = sheet.GetValue(m, j).ToString();
                            str = "\t\t\t\"" + sheet.GetValue(1, j).ToString() + "\":" + strasd;
                        }

                        if (j < sheet.Dimension.End.Column)
                        {
                            str += ",";
                        }
                        sw.WriteLine(str);
                    }
                    if(m < sheet.Dimension.End.Row)
                        sw.WriteLine("\t\t},");
                    else
                        sw.WriteLine("\t\t}");
                }
            }
        }

        sw.WriteLine("\t]");
        sw.Write("}");
        sw.Flush();
        sw.Close();

        if (fs != null)
            fs.Close();
        if (File.Exists(tmpExcelFile))
        {
            File.Delete(tmpExcelFile);
        }
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="excelPath"></param>
    /// <param name="jsonPath"></param>

    public static string[,] ReadExcel(string excelPath)
    {
        string[,] strGroup = new string[1,1];
        using (ExcelPackage package = new ExcelPackage(new FileStream(excelPath, FileMode.Open)))
        {
            for (int i = 1; i <= package.Workbook.Worksheets.Count; ++i)
            {
                ExcelWorksheet sheet = package.Workbook.Worksheets[i];
                strGroup = new string[sheet.Dimension.End.Row, sheet.Dimension.End.Column];
                for (int m = sheet.Dimension.Start.Row + 2, n = sheet.Dimension.End.Row; m <= n; m++)
                {
                    for (int j = sheet.Dimension.Start.Column, k = sheet.Dimension.End.Column; j <= k; j++)
                    {
                        strGroup[m, j] = sheet.GetValue(m, j).ToString();
                    }
                }
            }
        }
        return strGroup;
    }
}
